********************************************************************************
************ This program loads refinery prices ********************************
********************************************************************************

		 
************************
*** Rotterdam Prices ***
************************

//Load data ( 02.01.13 -30.12.14)
	import excel "$raw\02_Other_Datasets\RBRTEd.xls" , sheet("Data 1") cellrange(A4) clear

//Clean data (rename var, destring)
rename A date
rename B p_rotterdambbl
label variable p_rotterdambbl "Europe Brent Spot Price FOB in Dollar per Barrel"

// Keep only relevant dates
drop if date < d(01jan2013) | date > d(31dec2014)

//Dates
format date %tdDD/NN/CCYY

// Save data temporarily
save "$dta\interm_rott.dta", replace


********************************************
**Convert dollars/barrel - euros/100 litre**
********************************************

// Clean exchange rate data (rename, reformate and drop weekends)
import excel "$raw\02_Other_Datasets\Wechselkurs_euro-dollar", cellrange(A6) clear

rename A date
rename B xrt
drop C

// Keep only relevant dates
drop if date < d(01jan2013) | date > d(31dec2014)

format date %tdDD/NN/CCYY
destring xrt, replace
drop if mi(xrt)

// Save data
save "$dta\xrt_euro-dollar.dta", replace


************************
*** Rotterdam Prices ***
************************

// Merge crude oil price with exchange rate 
use "$dta\interm_rott.dta", clear
merge m:1 date using "$dta\xrt_euro-dollar.dta"
drop _merge

// Interpolate exchange rate on days where we have crude oil price, but not exch rate 
sort date 
replace xrt = xrt[_n-1] if xrt == .

// Interpolate crude oil price on days where we have exch rate, but not crude oil price (Rule: If price missing, usually because of holiday, use price of day before.) 
replace p_rotterdambbl = p_rotterdambbl[_n-1] if p_rotterdambbl == .

* Calculation of oil price in EUR per litre of gasoline / diesel:

* 1. One 42-gallon barrel of oil input is used to produce 19.36 gallons of 'finished motor gasoline', 12.26 gallons of 'distillate fuel oil' (assume this refers to diesel), 4.33 gallons of 'kerosene-type jet fuel'. 
* 2. The rest yields other products (i.e. petroleum coke, still gas, hydrocarbon gas liquids, etc.) which we assume to be of little value.
* 3. Gasoline:
*    --> under assumption in Step 2, [19.36/(19.36 + 12.26 + 4.33)] of oil price in $ per barrel corresponds to producing 19.36 gallons of gasoline
*    <-> 1/35.95 share of oil price in $ per barrel corresponds to producing 3.785411784 litres of gasoline  (Conversion from barrels to litres: 1 barrel = 42 US gallons, 1 US gallon = 3.785411784 litres)
*    <-> 1/(35.95*3.785411784) of oil price in $ per barrel corresponds to producing one litre of gasoline
*    <-> 1/(35.95*3.785411784*eur_usd) of oil price in $ per barrel [i.e. 'brent_price'] corresponds to oil price in EUR per litre of gasoline

* Sources for oil-gasoline/diesel conversion: https://www.eia.gov/tools/faqs/faq.php?id=327&t=9
* https://www.eia.gov/energyexplained/oil-and-petroleum-products/refining-crude-oil-inputs-and-outputs.php

* --> to convert Brent spot oil price from $ per barrel to EUR per litre of gasoline: 

	gen p_rotterdam = p_rotterdambbl/(35.95*3.785411784*xrt)*100				// crude oil price in Eurocent per litre 

//Drop auxiliary variable
	drop p_rotterdambbl xrt
	label variable p_rotterdam "Europe Brent Spot Price FOB in Euro per 100 litres"
	save "$dta\rotterdam_price", replace



// Erase intermediate dtas
	erase "$dta\interm_rott.dta"
	erase "$dta\xrt_euro-dollar.dta"

